﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data ;
using System.Windows.Forms;
using libDAL;

namespace libInventory.BUS
{
    public class clsBUSSalesTerm : IDisposable
    {
        private DataSet dsChargesDetails;
        private DataSet dsChargesHeader;
        private DataTable dtChargesList;
        private cls_DAL _DAL;
        private DataRow[] drOriginCharges;
        private DataRow[] drDestinationCharges;
        private DataRow[] drChargesHeader;
        private DataRow drOriginDestination;
        private DataRow drChargesHeaderEntry;
        private string strChargeHeader = string.Empty;
        private bool bolIsDatasourceLoaded = false;
        private DataRow[] drAdditionalChargesOrigin;
        private DataRow[] drAdditionalChargesDestination;
        private DataSet dsAdditionalCharges;
        private DataTable dtOrigin;

        public cls_DAL get_DAL
        {
            get
            {
                return _DAL;
            }
        }

        private string strTempConStrgrn = @"Data Source=193.9.200.92\SERVERARCHIE;Initial Catalog=ESB_FMSTransactions;User ID=abc;Password=1234";

        public bool get_OriginDestinationLoaded
        {
            get
            {
                return bolIsDatasourceLoaded;
            }
        }

        public bool get_CheckAdditionalChargesIDExists(string strDetailsID, bool bolOriginDestination)
        {
            bool bolStatus = false;
            try
            {
                DataRow[] drcheck = { };
                if (bolOriginDestination == true)
                {
                    drcheck = dsAdditionalCharges.Tables["AdditionalCharges-2"].Select("ChargeType = 'Origin' and SalesTermSetupAdditionalChargesID = '" + strDetailsID + "'");
                }
                else if (bolOriginDestination == false)
                {
                    drcheck = dsAdditionalCharges.Tables["AdditionalCharges-2"].Select("ChargeType = 'Destination' and SalesTermSetupAdditionalChargesID = '" + strDetailsID + "'");
                }
                if (drcheck.Length > 0)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckDetailsIDExists");
            }

            return bolStatus;
        }

        public bool get_CheckDetailsIDExists(string strDetailsID, bool bolOriginDestination)
        {
            bool bolStatus = false;
            try
            {
                DataRow[] drcheck = { };
                if (bolOriginDestination == true)
                {
                    drcheck = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Origin' and SalesTermSetupDetailsID = '" + strDetailsID + "'");
                }
                else if(bolOriginDestination == false)
                {
                    drcheck = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Destination' and SalesTermSetupDetailsID = '" + strDetailsID + "'");
                }
                if (drcheck.Length > 0)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckDetailsIDExists"); 
            }

            return bolStatus;
        }

        public clsBUSSalesTerm()
        {
            dsAdditionalCharges = new DataSet();
            dsChargesDetails = new DataSet();
            dsChargesHeader = new DataSet();
            _DAL = new cls_DAL(strTempConStrgrn);
            Queries = new strtQueries();
            dtOrigin = new DataTable();
        }
        //SalesTermSetupAdditionalCharges

        private struct strtQueries
        {
            public string get_QueryCharges
            {
                get
                {
                    return "select ccitemcode as [Item Code(s)], ccitemdes as [Description(s)] " +
                           "from mfchaitemmain " +
                           "group by ccitemcode, ccitemdes";
                }
            }

            public string get_QueryCheckHeader(string strHeaderID)
            {
                return "SELECT SalesTermSetupHeadersID, Description " +
                       "FROM  SalesTermSetupHeaders " +
                       "where SalesTermSetupHeadersID = '" + strHeaderID + "'";
            }

            public string get_QueryChargesDetails(string strChargesID)
            {

                string strQuery = string.Empty;
                string strTop = string.Empty;
                string strClause = string.Empty;
                try
                {
                    if (strChargesID.Trim() == string.Empty)
                    {
                        strTop = " top(0) ";
                    }
                    if (strChargesID.Trim() != string.Empty)
                    {
                        strClause = " WHERE LTRIM(RTRIM(SalesTermSetupHeadersID)) = '" + strChargesID.Trim() + "' ";
                    }
               
                    strQuery = "SELECT " + strTop + " SalesTermSetupHeadersID, SalesTermSetupDetailsID, ccitemcode, NoRating, RateM3, MinRateM3, " +
                                "MinAmountM3, RateWeight, MinRateWeight, MinAmountWeight, RateVolume, MinRateVolume, MinAmountVolume, " +
                                "RateQuantity, MinRateQuantity, MinAmountQuantity, InvoicePercentage,PerShipment , ExpiryFrom, ExpiryTo, " +
                                "ChargeType, DateTimeInserted, DateTimeUpdated, " +
                                "LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy  " +
                                "FROM SalesTermSetupDetails " + strClause;

                    if (strQuery.Contains(";") == true)
                    {
                        strQuery = string.Empty;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.InnerException.Message + " get_QueryChargesDetails");
                }
                return strQuery;
            }

            public string get_QueryAdditionalCharges(string strChargesID)
            {

                string strQuery = string.Empty;
                string strTop = string.Empty;
                string strClause = string.Empty;
                try
                {
                    if (strChargesID.Trim() == string.Empty)
                    {
                        strTop = " top(0) ";
                    }
                    if (strChargesID.Trim() != string.Empty)
                    {
                        strClause = " WHERE LTRIM(RTRIM(SalesTermSetupHeadersID)) = '" + strChargesID.Trim() + "' ";
                    }

                    strQuery = "SELECT " + strTop + " SalesTermSetupHeadersID, SalesTermSetupAdditionalChargesID, ccitemcode, NoRating, RateM3, MinRateM3, " +
                                "MinAmountM3, RateWeight, MinRateWeight, MinAmountWeight, RateVolume, MinRateVolume, MinAmountVolume, " +
                                "RateQuantity, MinRateQuantity, MinAmountQuantity, InvoicePercentage,PerShipment , ExpiryFrom, ExpiryTo, " +
                                "ChargeType, DateTimeInserted, DateTimeUpdated, " +
                                "LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy  " +
                                "FROM SalesTermSetupAdditionalCharges " + strClause;

                    if (strQuery.Contains(";") == true)
                    {
                        strQuery = string.Empty;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.InnerException.Message + " get_QueryChargesDetails");
                }
                return strQuery;
            }

            public string get_QueryChargesHeader(string strChargesID)
            {
                string strQuery = string.Empty;
                string strTop = string.Empty;
                string strClause = string.Empty;
                try
                {
                    if (strChargesID.Trim() == string.Empty)
                    {
                        strTop = " top(0) ";
                    }
                    if (strChargesID.Trim() != string.Empty)
                    {
                        strClause = " WHERE ltrim(rtrim(SalesTermSetupHeadersID)) = '" + strChargesID.Trim() + "'";
                    }
                    strQuery = "SELECT " + strTop + " SalesTermSetupHeadersID ,Description, INCOTERM, Type, Category, " +
                               "PortOrigin, PortDestination, EffectiveDateFrom, EffectiveDateTo, ServiceType,SalesTermID, " +
                               "DateTimeInserted, DateTimeUpdated, LastModifiedBy, HostNameIPAddress, OnUse, OnUseBy " +
                               "FROM SalesTermSetupHeaders " + strClause;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.InnerException.Message + " get_QueryChargesHeader");
                }
                return strQuery;
            }

            public string get_QueryFindChargesHeader(string strIncoterm, string strType, string strCategory,
                                                     string strOrgin, string strDestination, DateTime dteFrom,
                                                     DateTime dteTo)
            {
                string strQuery = string.Empty;

                if (strIncoterm != string.Empty)
                {
                    string strClause = "WHERE ltrim(rtrim(INCOTERM)) = '" + strIncoterm.Trim() + "' ";

                    if (strType.Trim() != string.Empty)
                    {
                        strClause += " AND ltrim(rtrim(Type)) = '" + strType.Trim() + "' ";
                    }
                    if (strCategory.Trim() != string.Empty)
                    {
                        strClause += " AND ltrim(rtrim(Category)) = '" + strCategory.Trim() + "' ";
                    }
                    if (strOrgin.Trim() != string.Empty)
                    {
                        strClause += " AND ltrim(rtrim(PortOrigin)) = '" + strOrgin.Trim() + "' ";
                    }
                    if (strDestination.Trim() != string.Empty)
                    {
                        strClause += " AND ltrim(rtrim(PortDestination)) = '" + strDestination.Trim() + "' ";
                    }
                    if (dteFrom != null)
                    {
                        strClause += " AND EffectiveDateFrom >= '" + dteFrom.ToString("MM/dd/yyyy") + "' ";
                    }
                    if (dteTo != null)
                    {
                        strClause += " AND EffectiveDateTo <= '" + dteTo.AddDays(1).ToString("MM/dd/yyyy") + "' ";
                    }


                    strQuery = "SELECT SalesTermSetupHeadersID AS HeaderID, Description AS Descriptions, INCOTERM AS Term, " +
                               "Type AS Type, Category AS Category, PortOrigin AS Origin, PortDestination AS Destination, " +
                               "convert(varchar(10),EffectiveDateFrom,103) as [Date From], convert(varchar(10),EffectiveDateTo,103) as [Date To], Status AS [Term Status] " +
                                "FROM SalesTermSetupHeaders " + strClause;
                }

                return strQuery;
            }
            public string get_QueryPort()
            {
                return "SELECT PortID, PortName " +
                       "FROM mfport";
            }

        }

        public bool get_checkNewRowSetup()
        {
            if (drOriginDestination == null)
            {
                return false;
            }
            else
            {
                return true;
            }       
        }

        public bool get_checkNewRoHeader()
        {
            if (this.drChargesHeaderEntry == null)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool set_AddDataRowOriginDestination()
        {
            bool bolStatus = false;
            try
            {
                if (this.drOriginDestination != null)
                {
                    if (this.drOriginDestination["ChargesAmountDetailsID"] != null && this.drOriginDestination["ChargesAmountDetailsID"] != DBNull.Value)
                    {
                        dsChargesDetails.Tables["DetailsCharges-0"].Rows.Add(this.drOriginDestination);
                        this.drOriginDestination = null;
                        bolStatus = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + " set_AddDataRow()");
            }
            return bolStatus;
        }

        public bool set_AddDataHeader()
        {
            bool bolStatus = false;
            try
            {
                if (this.drChargesHeaderEntry != null)
                {
                    dsChargesHeader.Tables["HeadersCharges-1"].Rows.Add(this.drChargesHeaderEntry);
                    this.drChargesHeaderEntry = null;
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + " set_AddDataRow()");
            }
            return bolStatus;
        }

        public string get_ChargeDescription(string strChargeID)
        {
            string strID = string.Empty;
            try
            {
                if (dtChargesList == null)
                {
                    get_Charges();
                    strID = get_ChargeDescription(strChargeID);
                }
                else
                {
                    //ccitemcode as [Item Code(s)], ccitemdes as [Description(s)] 
                    DataRow[] drCheck = dtChargesList.Select("[Item Code(s)]= '" + strChargeID + "'");
                    if (drCheck.Length > 0)
                    {
                        strID = clsGenFunction.get_checkStringNull(drCheck[0]["Description(s)"]);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_ChargeDescription"); 
            }

            return strID; 
           
        }

        public DataTable get_Charges()
        {
            try
            {
                dtChargesList = new DataTable();
                dtChargesList = _DAL.get_TableDetail(Queries.get_QueryCharges);
              
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + " get_Charges"); 
            }
            return this.dtChargesList;
        }

        public DataTable get_Ports()
        {
            DataTable dtPorts = new DataTable();
            try
            {
                dtPorts = _DAL.get_TableDetail(Queries.get_QueryPort());  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + " get_Ports"); 
            }

            return dtPorts;
        }

        private strtQueries Queries;

        public DataTable get_ChargesHeader(string strIncoterm, string strType, string strCategory,
                                                     string strOrgin, string strDestination, DateTime dteFrom,
                                                     DateTime dteTo)
        {
            DataTable dtCharges = new DataTable();
            try
            {
                dtCharges = _DAL.get_TableDetail(Queries.get_QueryFindChargesHeader(strIncoterm,strType,strCategory,strOrgin,strDestination,dteFrom,dteTo));   
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + " get_ChargesHeader"); 
            }

            return dtCharges;
        }

     
        
        public DataRow[] get_DestinationCharges
        {
            get
            {
                return drDestinationCharges;
            }
        }

        public DataRow[] getset_HeaderInfo
        {
            get
            {
                return drChargesHeader;
            }
            set
            {
                drChargesHeader = value;
            }
        }

        public DataRow[] get_OriginCharges
        {
            get
            {
                return drOriginCharges;
            }
        }

        public bool set_RecordsOriginDestination(string strChargeID)
        {
            bool bolStatus = false;
            try
            {
                if (get_CheckDetailsLoaded(strChargeID) == true 
                    && get_CheckHeaderLoaded(strChargeID) == true 
                    && get_CheckAdditionalServices(strChargeID) == true)
                {
                    bolStatus = true;
                    bolIsDatasourceLoaded = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message); 
            }

            return bolStatus;
        }

        public void set_OriginCharges(string strChargesDetailsID)
        {
            drOriginCharges = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Origin' and SalesTermSetupDetailsID = '" + strChargesDetailsID + "'");
        }

        public void set_DestinationCharges(string strChargesDetailsID)
        {
            drDestinationCharges = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Destination' and SalesTermSetupDetailsID = '" + strChargesDetailsID + "'");
        }

        public bool set_AddUpdateOriginDestination(DataTable dtOriginDestination)
        {
            bool bolStatus = false;
            try
            {
                DataRow[] drCheck = { };
                DataRow drInsert = dsChargesDetails.Tables["DetailsCharges-0"].NewRow();
                DataTable dtx = dtOriginDestination.Clone(); 
                DataRow[] drSource = dtOriginDestination.Select ();
                for (int intRows = 0; intRows < drSource.Length; intRows++)
                {
                    drCheck = dsChargesDetails.Tables["DetailsCharges-0"].Select("SalesTermSetupDetailsID = '" + drSource[intRows]["SalesTermSetupDetailsID"] + "'");
                    if (drCheck.Length > 0)
                    {
                        for (int intCol = 0; intCol < dtx.Columns.Count; intCol++)
                        {
                            drCheck[0][dtx.Columns[intCol].ColumnName] = drSource[intRows][dtx.Columns[intCol].ColumnName];
                        }
                    }
                    else
                    {
                        drInsert = dsChargesDetails.Tables["DetailsCharges-0"].NewRow();
                        for (int intCol = 0; intCol < dtOriginDestination.Columns.Count; intCol++)
                        {
                            drInsert[dtx.Columns[intCol].ColumnName] = drSource[intRows][dtx.Columns[intCol].ColumnName];
                        }
                        dsChargesDetails.Tables["DetailsCharges-0"].Rows.Add(drInsert);
                    }
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_AddUpdateOriginDestination");
            }
            return bolStatus;
        }

        public bool set_AddUpdateAdditionalChargesOriginDestination(DataTable dtOriginDestination)
        {
            bool bolStatus = false;
            try
            {
                //dsAdditionalCharges.Tables["AdditionalCharges-2"]
                DataRow[] drCheck = { };
                DataRow drInsert = dsAdditionalCharges.Tables["AdditionalCharges-2"].NewRow();
                DataTable dtx = dtOriginDestination.Clone();
                DataRow[] drSource = dtOriginDestination.Select();
                for (int intRows = 0; intRows < drSource.Length; intRows++)
                {
                    drCheck = dsAdditionalCharges.Tables["AdditionalCharges-2"].Select("SalesTermSetupAdditionalChargesID = '" + drSource[intRows]["SalesTermSetupAdditionalChargesID"] + "'");
                    if (drCheck.Length > 0)
                    {
                        for (int intCol = 0; intCol < dtx.Columns.Count; intCol++)
                        {
                            drCheck[0][dtx.Columns[intCol].ColumnName] = drSource[intRows][dtx.Columns[intCol].ColumnName];
                        }
                    }
                    else
                    {
                        drInsert = dsAdditionalCharges.Tables["AdditionalCharges-2"].NewRow();
                        for (int intCol = 0; intCol < dtOriginDestination.Columns.Count; intCol++)
                        {
                            drInsert[dtx.Columns[intCol].ColumnName] = drSource[intRows][dtx.Columns[intCol].ColumnName];
                        }
                        dsAdditionalCharges.Tables["AdditionalCharges-2"].Rows.Add(drInsert);
                    }
                }
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_AddUpdateOriginDestination");
            }
            return bolStatus;
        }

        public DataTable getset_Charges
        {
            get
            { 
                return dtOrigin;
            }
            set
            {
                dtOrigin = value;
            }
        }

        private bool get_CheckDetailsLoaded(string strChargeID)
        {
            bool bolStatus = false;
            try
            {
                if (_DAL.set_DataWCardinality_ds(Queries.get_QueryChargesDetails(strChargeID), ref dsChargesDetails, "DetailsCharges-0") == true)
                {
                    dtOrigin = dsChargesDetails.Tables["DetailsCharges-0"];
                    drOriginCharges = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Origin'");
                    drDestinationCharges = dsChargesDetails.Tables["DetailsCharges-0"].Select("ChargeType = 'Destination'");
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + "get_CheckDetailsLoaded");
            }
            return bolStatus;
        }

        public DataRow getset_NewRowDetailsCharge
        {
            get
            {
                return drOriginDestination; 
            }
            set
            {
                drOriginDestination = value;
            }
        }

        public DataRow getset_NewRowHeaderCharge
        {
            get
            {
                return this.drChargesHeaderEntry; 
            }
            set
            {
                this.drChargesHeaderEntry = value;
            }
        }

        public bool get_SetNewRowDetailsCharges()
        {
            bool bolStatus = false;
            try
            {
                if (dsChargesDetails.Tables.Count > 0)
                {
                    drOriginDestination = this.dsChargesDetails.Tables["DetailsCharges-0"].NewRow();
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_SetNewRowDetailsCharges"); 
            }
           
            return bolStatus;
        }

        public bool get_SetNewRowHeaderCharges()
        {
            bool bolStatus = false;
            if (dsChargesHeader.Tables.Count > 0)
            {
                this.drChargesHeaderEntry = this.dsChargesHeader.Tables["HeadersCharges-1"].NewRow();
                bolStatus = true;
            }
            return bolStatus;
        }

        public bool set_HeaderInfo(string strHeaderID)
        {
            bool bolStatus = false;
            try
            {
                drChargesHeader = dsChargesHeader.Tables["HeadersCharges-1"].Select("SalesTermSetupHeadersID = '" + strHeaderID + "'");
                if (drChargesHeader.Length > 0)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_HeaderInfo"); 
            }
            return bolStatus;
        }

        public DataRow[] get_AdditionalChargesDestination
        {
            get
            {
                return drAdditionalChargesDestination; 
            }
            set
            {
                drAdditionalChargesDestination = value;
            }
        }

        public DataRow[] get_AdditionalChargesOrigin
        {
            get
            {
                return drAdditionalChargesOrigin;
            }
            set
            {
                drAdditionalChargesOrigin = value;
            }
        }

        private bool get_CheckAdditionalServices(string strChargeID)
        {
             bool bolStatus = false;
             try
             {
                 if(_DAL.set_DataWCardinality_ds(Queries.get_QueryAdditionalCharges(strChargeID), ref dsAdditionalCharges, "AdditionalCharges-2") == true)
                 {
                     drAdditionalChargesOrigin = dsAdditionalCharges.Tables["AdditionalCharges-2"].Select("ChargeType = 'Origin'");
                     drAdditionalChargesDestination = dsAdditionalCharges.Tables["AdditionalCharges-2"].Select("ChargeType = 'Destination'");
                     bolStatus = true;
                 }
             }
             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message + " get_CheckAdditionalServices"); 
             }
             return bolStatus;
        }

        private bool get_CheckHeaderLoaded(string strChargeID)
        {
            bool bolStatus = false;
            try
            {
                if (_DAL.set_DataWCardinality_ds(Queries.get_QueryChargesHeader(strChargeID), ref dsChargesHeader, "HeadersCharges-1"))
                {
                    drChargesHeader = dsChargesHeader.Tables["HeadersCharges-1"].Select();
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message + "get_CheckHeaderLoaded");
            }
            return bolStatus;
        }

        public void Dispose()
        {
            dsAdditionalCharges = null;
            dsChargesDetails = null;
            dsChargesHeader = null;
            dtChargesList = null;
            dtOrigin = null;
           // _DAL.Dispose();
            GC.Collect ();
        }

        //this function must check in serverside
        public bool get_CheckHeaderExists(string strHeaderID)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.get_CheckAnythingExists(Queries.get_QueryCheckHeader(strHeaderID));
            }
            catch (Exception ex)
            { 
                MessageBox.Show(ex.Message + " get_CheckHeaderExists"); 
            }
            return bolStatus ;
        }

        public bool set_CommitRecordsHeader()
        {
            return _DAL.Update_batch_ds(ref dsChargesHeader, "HeadersCharges-1"); 
        }

        public bool set_CommitRecordsAdditionalCharges()
        {
            return _DAL.Update_batch_ds(ref this.dsAdditionalCharges, "AdditionalCharges-2"); 
        }

        public bool set_CommitRecordsDetails()
        {
           return _DAL.Update_batch_ds(ref dsChargesDetails, "DetailsCharges-0");  
        }
    }
}
